﻿Imports System.Data.SqlClient
Module HamDungChung
    Public Function TinhSoLuongTonKho(ByVal MaPhieu As String, ByVal MaHH As String, ByVal NgayCT As DateTime) As Double
        Dim myConn As New SqlConnection(chuoiketnoi)
        Dim myTable As New DataTable
        Dim myComm As New SqlCommand
        Dim tonkho As Double = 0
        Dim strSql As String = "Select SLTonDK, GTTonDK, GiaVonDK from CTHangHoa where MaHH = @MaHH and Thang = @Thang and Nam = @Nam "
        Try
            myConn.Open()
            myComm.Connection = myConn
            myComm.CommandText = strSql
            myComm.Parameters.AddWithValue("@MaHH", MaHH)
            myComm.Parameters.AddWithValue("@Thang", NgayCT.Month)
            myComm.Parameters.AddWithValue("@Nam", NgayCT.Year)
            Dim myAdapter As New SqlDataAdapter(myComm)
            myAdapter.Fill(myTable)
            If myTable.Rows.Count > 0 Then
                tonkho = myTable.Rows(0)(0)
            End If

            myComm.Parameters.Clear()
            strSql = "Select "
            strSql &= " ct.MaPhieuCT, "
            strSql &= " sc.LoaiCT, "
            strSql &= " ct.SoLuong, "
            strSql &= " case "
            strSql &= " when ct.USD = 1 then sc.TyGia * ct.SoLuong * ct.DonGia "
            strSql &= "	else ct.SoLuong * ct.DonGia "
            strSql &= " end as TienHang "
            strSql &= " from"
            strSql &= " CTSoCai ct"
            strSql &= " inner join SoCai sc on ct.MaPhieu = sc.MaPhieu"
            strSql &= " where "
            strSql &= " datepart(Month, sc.NgayCT) = @Thang "
            strSql &= " and datePart(Year, sc.NgayCT) = @Nam "
            strSql &= " and ct.MaHH = @MaHH "
            strSql &= " and NgayCT <= convert(DATETIME, @NgayCT, 103) "
            If MaPhieu <> "" Then
                strSql &= " and sc.MaPhieu <> @MaPhieu "
                myComm.Parameters.AddWithValue("@MaPhieu", MaPhieu)
            End If
            strSql &= " order by sc.NgayCT ASC, sc.LoaiCT"
            myComm.CommandText = strSql
            myComm.Parameters.AddWithValue("@MaHH", MaHH)
            myComm.Parameters.AddWithValue("@Thang", NgayCT.Month)
            myComm.Parameters.AddWithValue("@Nam", NgayCT.Year)
            myComm.Parameters.AddWithValue("@NgayCT", NgayCT.ToString("dd/MM/yyyy"))
            myAdapter = New SqlDataAdapter(myComm)
            myTable = New DataTable()
            myAdapter.Fill(myTable)
            If myTable.Rows.Count > 0 Then
                For Each row As DataRow In myTable.Rows
                    If row(1) = "PNK" Then
                        tonkho = tonkho + row(2)
                    Else
                        tonkho = tonkho - row(2)
                    End If
                Next
            End If
        Catch ex As Exception
            Throw ex
        End Try
        myConn.Close()
        myConn.Dispose()
        Return tonkho
    End Function

    Public Function NgayChungTuHopLe(ByVal NgayCT As DateTime) As Boolean
        Dim result As Boolean = True
        Dim myConn As New SqlConnection(chuoiketnoi)
        Dim strSql As String = "select Top 1"
        strSql &= " case when convert(datetime, @NgayCT,103) <= convert(datetime,'01/' + cast(thang as varchar) + '/' + cast(nam as varchar),103) then 0 else 1 end as valid "
        strSql &= "    from KhoaSo "
        strSql &= " Order by convert(datetime,'01/' + cast(thang as varchar) + '/' + cast(nam as varchar),103) desc"
        Dim myTable As New DataTable
        Try
            myConn.Open()
            Dim myAdapter As New SqlDataAdapter(strSql, myConn)
            myAdapter.SelectCommand.Parameters.AddWithValue("@NgayCT", NgayCT.ToString("01/MM/yyyy"))
            myAdapter.Fill(myTable)
            If myTable.Rows.Count > 0 AndAlso myTable.Rows(0)(0) = 0 Then
                result = False
            End If
        Catch ex As Exception

        End Try
        myConn.Close()
        myConn.Dispose()
        Return result
    End Function

    Dim ChuSo() As String = {"không", "một", "hai", "ba", "bốn", "năm", "sáu", "bảy", "tám", "chín"}
    Dim DonVi() As String = {"đơn vị", "ngàn", "triệu", "tỉ"}

    Function DocSo(ByVal so As String) As String
        so = so.TrimStart("0")
        If so.Length = 0 Then Return ""
        so = so.Replace(" ", "").Replace(",", "").Replace(".", "")
        Dim kq As String = ""
        Dim chieudai As Integer = so.Length
        Dim sonhom As Integer = Math.Ceiling(chieudai / 3)
        Dim nhom(sonhom - 1) As String
        Dim j As Integer = chieudai - (sonhom - 1) * 3
        nhom(0) = so.Substring(0, j)
        For i As Integer = 1 To sonhom - 1
            nhom(i) = so.Substring(j + (i - 1) * 3, 3)
        Next

        For i As Integer = 0 To nhom.Length - 1
            kq &= docnhom3so(nhom(i), tinhdonvi(nhom.Length - 1 - i)) & " "
        Next

        Return kq.Trim.Replace("  ", " ")
    End Function

    Function tinhdonvi(ByVal n As Integer) As String
        If n <= 3 Then
            Return DonVi(n)
        Else
            Return tinhdonvi(n - 3) & " " & DonVi(3)
        End If
    End Function

    Function docnhom3so(ByVal số As String, ByVal đơnvị As String) As String
        Dim kq As String = ""
        Dim l As Integer = số.Length
        số = số.PadLeft(3, "0")
        If số = "000" Then Return ""

        If số.StartsWith("00") Then Return đọcsốhàngđơnvị(Val(số(2)), 0, l = 3) & " " & đơnvị

        kq &= đọcsốhàngtrăm(Val(số(0)), l = 3)
        kq &= đọcsốhàngchục(Val(số(1)))
        kq &= đọcsốhàngđơnvị(Val(số(2)), Val(số(1)), l = 3)

        Return kq & " " & đơnvị
    End Function

    Function đọcsốhàngtrăm(ByVal i As Integer, ByVal đọcsốkhông As Boolean) As String
        If i = 0 Then
            If đọcsốkhông Then
                Return "không trăm "
            Else
                Return ""
            End If
        Else
            Return ChuSo(i) & " trăm "
        End If
    End Function

    Function đọcsốhàngchục(ByVal i As Integer) As String
        Select Case i
            Case 0
                Return ""
            Case 1
                Return "mười "
            Case Else
                Return ChuSo(i) & " mươi "
        End Select
    End Function

    Function đọcsốhàngđơnvị(ByVal i As Integer, ByVal hàngchục As Integer, ByVal đọcchữlẻ As Boolean) As String
        If i = 0 Then
            Return ""
        Else
            If hàngchục = 0 Then
                If đọcchữlẻ Then
                    Return "lẻ " & ChuSo(i)
                Else
                    Return ChuSo(i)
                End If
            Else
                Select Case i
                    Case 1
                        If hàngchục = 1 Then
                            Return "một"
                        Else
                            Return "mốt"
                        End If
                    Case 5
                        Return "lăm"
                    Case Else
                        Return ChuSo(i)
                End Select
            End If
        End If
    End Function
End Module
